package com.gem.growingtree.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.gem.growingtree.db.DBLink;
import com.gem.growingtree.model.ClassAccount;
import com.gem.growingtree.model.PageBean3;
import com.gem.growingtree.model.ShowClasses;

public class ClassAccountService {
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;

	public void close(){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
		if(pstmt != null){
			try {
				pstmt.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
	}


	public PageBean3 findByPage(int pageCode, int pageSize) throws Exception {
		if(pageCode<=0)
		{
			pageCode=1;
		}
		PageBean3 pb = new PageBean3();
		ClassAccount classAccount = null;
		conn = DBLink.getConnection();
		String sql = "select count(classId) from classes";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			pb.setAllCount(rs.getInt(1));
		}
		pb.setPageSize(pageSize);
		pb.setPageCode(pageCode);

		sql = "SELECT classId, className, schoolName,teacherName,classSize,classroom FROM classes,teacher WHERE classes.teacherId = teacher.teacherId   limit ?,?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, (pageCode-1)*pageSize);
		pstmt.setInt(2, pageSize);
		rs = pstmt.executeQuery();
		while(rs.next()){
			String classId = rs.getString(1);
			String className = rs.getString(2);
			String schoolName = rs.getString(3);
			String teacherName = rs.getString(4);
			String classSize = rs.getString(5);
			String classroom = rs.getString(6);
			classAccount = new ClassAccount(classId,className,schoolName,teacherName,classSize,classroom);
			pb.getList().add(classAccount);
		}
		return pb;
	}

	public PageBean3 findByPage2(int pageCode, int pageSize,String str) throws Exception {
		PageBean3 pb = new PageBean3();
		ClassAccount classAccount = null;
		conn = DBLink.getConnection();
		String sql = "select count(classId) from classes";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			pb.setAllCount(rs.getInt(1));
		}
		pb.setPageSize(pageSize);
		pb.setPageCode(pageCode);

		sql = "SELECT classId, className, schoolName,teacherName,classSize,classroom FROM classes,teacher WHERE classes.teacherId = teacher.teacherId  and classId=? limit ?,?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, str);
		pstmt.setInt(2, (pageCode-1)*pageSize);
		pstmt.setInt(3, pageSize);
		rs = pstmt.executeQuery();
		while(rs.next()){
			String classId = rs.getString(1);
			String className = rs.getString(2);
			String schoolName = rs.getString(3);
			String teacherName = rs.getString(4);
			String classSize = rs.getString(5);
			String classroom = rs.getString(6);
			classAccount = new ClassAccount(classId,className,schoolName,teacherName,classSize,classroom);
			pb.getList().add(classAccount);
		}
		return pb;
	}
	public boolean updateClasses(int parseInt, ShowClasses showClasses)
			throws SQLException {
		boolean flag = false;
		conn = DBLink.getConnection();
		String sql = "update classes set className=?,schoolName=?,teacherName=?,classroom=?,classSize where classId=?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, showClasses.getClassName());
		pstmt.setString(2, showClasses.getSchoolName());
		pstmt.setString(3, showClasses.getTeacherName());
		pstmt.setString(4, showClasses.getClassroom());
		pstmt.setString(5, showClasses.getClassSize());
		int rows = pstmt.executeUpdate(sql);
		if (rows == 1) {
			flag = true;
		}
		return flag;
	}

	public boolean deleteClassInfo(String classId ) {
		boolean flag = false;
		conn = DBLink.getConnection();
		String sql = "delete from classes where classId in(" + classId + ")";
		try {
			pstmt = conn.prepareStatement(sql);
			int rows = pstmt.executeUpdate(sql);
			if (rows > 0)
			{
				flag = true;
				System.out.println(flag);
				System.out.println("班级信息删除成功");
			} else {
				System.out.println("删除失败");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}	
}

